using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TouchWinApp.Code
{
    public class SqlServerHelper
    {
        private static readonly string conStr = "";//todo 数据库连接字符串，自己获取

        #region  ado.net方法

        public static DataTable ExecuteDataTable(string sqlStr, params SqlParameter[] pams)
        {
            return ExecuteDataSet(sqlStr, pams).Tables[0];
        }

        public static DataSet ExecuteDataSet(string sqlStr, params SqlParameter[] pams)
        {
            //using (SqlDataAdapter adapter=new SqlDataAdapter(sqlStr,conStr))
            //{
            //    adapter.SelectCommand.Parameters.AddRange(pams);
            //    DataSet ds = new DataSet();
            //    adapter.Fill(ds);
            //    return ds;
            //}

            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, conn))
                {
                    adapter.SelectCommand.Parameters.AddRange(pams);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static bool ExecuteProc(string procName, params SqlParameter[] pams)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(procName, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(pams);
                    conn.Open();
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }


        /// <summary>
        /// 执行单个sql
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlStr, params SqlParameter[] pams)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                    {
                        cmd.Parameters.AddRange(pams);
                        return cmd.ExecuteNonQuery() ;
                    }
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 批量执行sql,带事务
        /// </summary>
        /// <param name="sqlStrList"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(List<string> sqlStrList)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    using (SqlTransaction tx = conn.BeginTransaction())
                    {
                        cmd.Transaction = tx;
                        try
                        {
                            for (var index = 0; index < sqlStrList.Count; index++)
                            {
                                var _sql = sqlStrList[index];
                                cmd.CommandText = _sql;
                                cmd.ExecuteNonQuery();
                            }
                            tx.Commit();
                            return true;
                        }
                        catch (SqlException ex)
                        {
                            tx.Rollback();
                            throw new Exception(ex.Message);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条sql语句
        /// </summary>
        /// <param name="sqlStr">sql语句，{0} 为where条件</param>
        /// <param name="dicWhere">条件集合</param>
        /// <returns></returns>
        public static int ExecuteSQL(string sqlStr, Dictionary<string, object> dicWhere)
        {
            List<SqlParameter> sqlParams=new List<SqlParameter>();
            string whereSql = dicFill_where(dicWhere, ref sqlParams);

            return ExecuteNonQuery(string.Format(sqlStr, whereSql), sqlParams.ToArray());
        }

        /// <summary>
        /// 批量执行sql,带事务
        /// </summary>
        /// <param name="sqlStrList"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuery(List<string> sqlStrList, List<SqlParameter[]> pams)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                SqlCommand cmd;

                using (SqlTransaction tx = conn.BeginTransaction())
                {

                    try
                    {
                        for (var index = 0; index < sqlStrList.Count; index++)
                        {
                            var _sql = sqlStrList[index];

                            cmd = new SqlCommand();
                            cmd.Connection = conn;
                            cmd.Transaction = tx;

                            cmd.CommandText = _sql;
                            if (pams[index] != null)
                                cmd.Parameters.AddRange(pams[index]);
                            cmd.ExecuteNonQuery();
                        }

                        tx.Commit();
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        tx.Rollback();
                        throw new Exception(ex.Message);
                    }
                }

            }
        }

        public static object ExecuteScalar(string sqlStr, params SqlParameter[] pams)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
                {
                    try
                    {
                        cmd.Parameters.AddRange(pams);
                        return cmd.ExecuteScalar();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
        }



        #endregion

        /// <summary>
        /// 简单查询数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">字段</param>
        /// <param name="dicWhere">条件</param>
        /// <returns></returns>
        public static DataTable GetDataTable( string tableName, string columns, Dictionary<string, object> dicWhere)
        {
            string sql = string.Format("select {0} from {1} ", columns, tableName);

            List<SqlParameter> sqlParams = new List<SqlParameter>();

            if (dicWhere != null)
            {
                string whereSql = dicFill_where(dicWhere, ref sqlParams);

                return ExecuteDataTable(sql + " where 1=1 " + whereSql, sqlParams.ToArray());
            }
            else
            {
                return ExecuteDataTable(sql);
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sqlStr">sql</param>
        /// <param name="dicWhere">条件</param>
        /// <param name="whereSql">where 条件,加and (注意sql注入攻击)</param>
        /// <param name="parList">扩展参数</param>
        /// <returns></returns>
        public static DataTable GetDataTableBySql( string sqlStr, Dictionary<string, object> dicWhere, string whereSql = "", List<SqlParameter> parList = null)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            string _whereSql = dicFill_where(dicWhere, ref sqlParams) + whereSql;

            if (parList != null && parList.Count > 0)
            {
                sqlParams.AddRange(parList);
            }

            if (sqlParams.Count > 0)
            {
                return ExecuteDataTable(string.Format(sqlStr + " {0} ", " where 1=1 " + _whereSql), sqlParams.ToArray());
            }
            else
            {
                return ExecuteDataTable(string.IsNullOrEmpty(whereSql) ? sqlStr : sqlStr + whereSql);
            }
        }


        /// <summary>
        /// 分页取数据
        /// </summary>
        /// <param name="tableName">前台子查询生成表名字</param>
        /// <param name="dicWhere">where条件字段集合</param>
        /// <param name="whereSql">where条件，加 and (注意sql注入)</param>
        /// <param name="orderbyColumn">排序字段</param>
        /// <param name="pageIndex">当前页码(从0开始)</param>
        /// <param name="pageSize">一页显示多少条记录</param>
        /// <param name="recordCount">返回总记录</param>
        /// <param name="cte">cte的sql</param>
        /// <returns></returns>
        public static DataTable GetDataPage( string tableName, Dictionary<string, object> dicWhere, string whereSql, string orderbyColumn, int pageIndex, int pageSize, out int recordCount, string cte = "")
        {
            List<SqlParameter> par = new List<SqlParameter>();

            StringBuilder where = new StringBuilder();
            where.Append(" where 1=1 ");
            where.Append(dicFill_where(dicWhere, ref par) + whereSql);


            string tableSql = tableName.Contains("select ") ?
                string.Format(" ( " + tableName + " {0} ) pageTable ", where.ToString()) :
                string.Format(" ( select * from " + tableName + " {0} ) pageTable ", where.ToString());

            string sql = string.Format(@"{5} 
                       select  {0},row_number() over(order by {1}) rid into #tt from {2} 
                               Order By {1}  select * from  #tt where rid> {3} and rid<={4} ;select count(1) from #tt ;drop table  #tt ",

                " * ", orderbyColumn, tableSql, pageSize * pageIndex, pageSize * (pageIndex + 1), cte);


            DataSet ds = par.Count > 0 ? ExecuteDataSet(sql, par.ToArray()) : ExecuteDataSet(sql);

            recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());

            return ds.Tables[0];
        }

        /// <summary>
        /// 封装where参数sql语句
        /// </summary>
        /// <param name="dicWhere"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        private static string dicFill_where(Dictionary<string, object> dicWhere, ref List<SqlParameter> par)
        {
            StringBuilder whereSql = new StringBuilder();

            if (dicWhere != null && dicWhere.Count > 0)
            {
                string keyName;
                foreach (KeyValuePair<string, object> keyDic in dicWhere)
                {
                    keyName = getParameterName(keyDic.Key);
                    if (keyDic.Value.ToString().Contains("%") ||
                        keyDic.Value.ToString().Contains("_") ||
                        (keyDic.Value.ToString().Contains("[") && keyDic.Value.ToString().Contains("]"))
                    ) //只要是含有 % _  [ ]  都是按照模糊查询
                    {
                        whereSql.AppendFormat(" and {0} like @{1} ", keyDic.Key, keyName);
                    }
                    else
                    {
                        whereSql.AppendFormat(" and {0} = @{1} ", keyDic.Key, keyName);
                    }

                    par.Add(new SqlParameter(string.Format("@{0}", keyName), keyDic.Value));
                }
            }

            return whereSql.ToString();
        }

        /// <summary>
        /// 把字段名称 [dbo].[new].[title] ，取字段名title
        /// </summary>
        /// <param name="key">字段</param>
        /// <returns></returns>
        private static string getParameterName(string key)
        {
            var temp = key.Split('.');
            StringBuilder sb = new StringBuilder(temp[temp.Length - 1]);
            sb.Replace("[", string.Empty).Replace("]", string.Empty);
            return sb.ToString();
        }

        /// <summary>
        /// 动态添加数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">参数和对应值集合</param>
        /// <returns>新添加数据id</returns>
       
        public static int InsertDB( string tableName, Dictionary<string, object> dic)
        {
            StringBuilder columns = new StringBuilder();
            StringBuilder columnParameters = new StringBuilder();
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            if (dic.Count > 0)
            {
                foreach (KeyValuePair<string, object> keyDic in dic)
                {
                    columns.AppendFormat(",{0}", keyDic.Key);
                    columnParameters.AppendFormat(",@{0}", getParameterName(keyDic.Key));
                    sqlParams.Add(new SqlParameter(string.Format("@{0}", getParameterName(keyDic.Key)), keyDic.Value));
                }
            }
            else
            {
                return 0;
            }

            string sql =
                string.Format("insert into {0}({1}) values ({2});select @@IDENTITY",
                    tableName, columns.ToString().Substring(1), columnParameters.ToString().Substring(1));

            return Convert.ToInt32(ExecuteScalar(sql, sqlParams.ToArray()));
        }

        /// <summary>
        /// 动态更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">参数和对应值集合</param>
        /// <param name="whereSql">where 条件(注意sql注入攻击)</param>
        /// <returns>是否更新成功！</returns>
      
        public static bool UpdateDB( string tableName, Dictionary<string, object> dic, string whereSql)
        {
            StringBuilder columns = new StringBuilder();

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            string keyName;
            if (dic != null && dic.Count > 0)
            {
                foreach (KeyValuePair<string, object> keyDic in dic)
                {
                    keyName = getParameterName(keyDic.Key);
                    columns.AppendFormat(",{0}=@{1}", keyDic.Key, keyName);
                    sqlParams.Add(new SqlParameter(string.Format("@{0}", keyName), keyDic.Value));
                }
            }
            else
            {
                return false;
            }

            string sql =
                string.Format("update {0} set {1} where {2}",
                    tableName, columns.ToString().Substring(1), whereSql);

            return ExecuteNonQuery(sql, sqlParams.ToArray())>0 ;
        }


        /// <summary>
        /// 动态更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">参数和对应值集合</param>
        /// <param name="dicWhere">where条件参数和对应值集合</param>
        /// <returns>是否更新成功！</returns>
      
        public static bool UpdateDB( string tableName, Dictionary<string, object> dic, Dictionary<string, object> dicWhere)
        {
            StringBuilder columns = new StringBuilder();
            StringBuilder wheresSql = new StringBuilder();
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            string keyName;
            if (dic != null && dic.Count > 0)
            {
                foreach (KeyValuePair<string, object> keyDic in dic)
                {
                    keyName = getParameterName(keyDic.Key);
                    columns.AppendFormat(",{0}=@{1}", keyDic.Key, keyName);
                    sqlParams.Add(new SqlParameter(string.Format("@{0}", keyName), keyDic.Value));
                }
            }
            else
            {
                return false;
            }

            if (dicWhere != null && dicWhere.Count > 0)
            {
                foreach (KeyValuePair<string, object> keyDic in dicWhere)
                {
                    keyName = getParameterName(keyDic.Key);
                    wheresSql.AppendFormat(" and {0}=@{1}", keyDic.Key, keyName);
                    sqlParams.Add(new SqlParameter(string.Format("@{0}", keyName), keyDic.Value));
                }
            }
            else
            {
                return false;
            }

            string sql =
                string.Format("update {0} set {1} where 1=1 {2}",
                    tableName, columns.ToString().Substring(1), wheresSql.ToString().Substring(1));

            return ExecuteNonQuery(sql, sqlParams.ToArray()) > 0;
        }


        /// <summary>
        /// 查询是否存在此记录
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dicWhere">条件</param>
        /// <param name="whereSql">条件(加 and)</param>
        /// <returns></returns>
       
        public static bool Exists( string tableName, Dictionary<string, object> dicWhere, string whereSql = "")
        {
            string sql = string.Format("select count(1) from {0} where 1=1 ", tableName);

            List<SqlParameter> sqlParams = new List<SqlParameter>();

            string whereSql2 = dicFill_where(dicWhere, ref sqlParams) + whereSql;

            return Convert.ToInt32(ExecuteScalar(sql + whereSql2, sqlParams.ToArray()))>0 ;
        }

    }
}
